#!/usr/bin/env python3
# -*- coding: utf8 -*-

import sqlite3

sql_table = '''
create table if not exists  voa51 (
	title TEXT not null, 
	url TEXT  PRIMARY KEY , 
	content TEXT,
	date INTEGER, 
	mp3 TEXT, 
	download INTEGER )
'''
sql_query = 'select title, download from voa51'
db = sqlite3.connect('voa51.db')

sql_not_download = "select mp3 from voa51 where download IS NULL "
sql_update_download = "update voa51 set download = 1 where mp3 = (?)"
def table_init():
    with db:
        db.execute(sql_table)


purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 'text221'),
('2006-03-28', 'BUY2', 'IBM', 1000, 'text221'),
('2006-03-29', 'BUY', 'IBM', 1000, 'text221'),
             ]

def update_data(d):
    try:
        with db:
            db.executemany('insert or ignore into voa51 (title, url, content, date, mp3) values(?,?,?,?,?)', d)
    except sqlite3.IntegrityError:
        print("error happen")


def find_not_download():
    """
    :return: a list which contains all mp3 link to download
    """
    rv = []
    with db:
        rv = list(map(lambda x: x[0], db.execute(sql_not_download)))
    return rv


def update_download(url:str):
    with db:
        db.execute(sql_update_download,(url,))


def main():
    with db:
        for row in db.execute(sql_query):
            print(row)
        print(find_not_download())
    pass

if __name__ == '__main__':
    main()